using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Collections;
using PreguntaAlExperto.TransporterObjectsFramework;

namespace PreguntaAlExperto.DataFramework
{
    public class DataAccess
    {

        #region Private variables
        private SqlConnection _connection;
        private SqlCommand _command;
        private int _totalRecords;
        private SqlTransaction _sqlTransaction;
        #endregion

        #region Class handlers
        /// <summary>
        /// Constructor. Initialize the class.
        /// </summary>
        public DataAccess()
        {
            _connection = new SqlConnection(ConfigurationHandler.getConnectionString);
            _command = new SqlCommand();
            _totalRecords = 0;
        }

        ~DataAccess()
        {
            _command.Dispose();
            _connection.Dispose();
        }
        #endregion

        #region Basic Methods
        public int Actualize(string query, SearchCriteria item)
        {
            _command.Connection = _connection;

            if (ConfigurationHandler.getQueryType(query) == "sp")
                _command.CommandType = System.Data.CommandType.StoredProcedure;
            else
                _command.CommandType = System.Data.CommandType.Text;

            _command.CommandText = ConfigurationHandler.getQuery(query);
            _connection.Open();

            if (item != null)
            {
                Dictionary<string, string> _local = new Dictionary<string, string>();
                Dictionary<string, string> _size = new Dictionary<string, string>();
                _local = ConfigurationHandler.getParameter(query);
                _size = ConfigurationHandler.getParameterSize(query);
                foreach (string _paramName in item.getParameterNameCollection())
                    _command.Parameters.Add(addParameter(_paramName,
                                item.getParameter(_paramName),
                                _local[_paramName],
                                _size[_paramName]));
            }

            int _rowAffected;
            _rowAffected = _command.ExecuteNonQuery();
            _connection.Close();

            return _rowAffected;
        }

        /// <summary>
        /// Execute a basic Select query and returns raw data for be handled by DTO object.
        /// </summary>
        /// <param name="query">Represent the key of the query.</param>
        /// <param name="searchCriteria">List of parameter to be added into the query.</param>
        /// <returns></returns>
        public object[] Select(string query, SearchCriteria searchCriteria)
        {
            _command.Connection = _connection;

            if (ConfigurationHandler.getQueryType(query) == "sp")
                _command.CommandType = System.Data.CommandType.StoredProcedure;
            else
                _command.CommandType = System.Data.CommandType.Text;

            string _commandText = ConfigurationHandler.getQuery(query);

            if (searchCriteria != null)
            {
                if (searchCriteria.ColumnFiler.Length != 0)
                    _commandText += " AND " + searchCriteria.ColumnFiler + " LIKE '%" + searchCriteria.FilterValue + "%'";

                if (searchCriteria.OrderField.Length != 0)
                    _commandText += " order by " + searchCriteria.OrderField + " " + searchCriteria.Order;
            }

            _command.CommandText = _commandText;
            _command.Parameters.Clear();
            _connection.Open();

            if (searchCriteria != null)
            {
                Dictionary<string, string> _local = new Dictionary<string, string>();
                _local = ConfigurationHandler.getParameter(query);
                foreach (string _paramName in searchCriteria.getParameterNameCollection())
                    _command.Parameters.Add(addParameter(_paramName,
                                searchCriteria.getParameter(_paramName),
                                _local[_paramName], null));
            }

            SqlDataReader _reader;
            _reader = _command.ExecuteReader();

            ArrayList _data = new ArrayList();
            int _itemCounter = 0;

            if (searchCriteria != null && searchCriteria.ActualPage != 0 && searchCriteria.PageSize != 0)
            {
                while (_reader.Read())
                {
                    _itemCounter++;
                    if (_itemCounter <= (searchCriteria.ActualPage * searchCriteria.PageSize)
                        && _itemCounter > ((searchCriteria.ActualPage * searchCriteria.PageSize) - searchCriteria.PageSize))
                    {
                        ArrayList _columns = new ArrayList();
                        for (short i = 0; i < _reader.FieldCount; i++)
                            _columns.Add(_reader.GetValue(i));
                        _data.Add(_columns);
                    }
                }
            }
            else
            {
                while (_reader.Read())
                {
                    _itemCounter++;
                    ArrayList _columns = new ArrayList();
                    for (short i = 0; i < _reader.FieldCount; i++)
                        _columns.Add(_reader.GetValue(i));
                    _data.Add(_columns);
                }
            }

            this.getRecordsAfected = _itemCounter;

            _reader.Close();
            _connection.Close();

            return _data.ToArray();
        }

        public object SelectOne(string query, SearchCriteria item)
            {
                _command.Connection = _connection;

                if (ConfigurationHandler.getQueryType(query) == "sp")
                    _command.CommandType = System.Data.CommandType.StoredProcedure;
                else
                    _command.CommandType = System.Data.CommandType.Text;

                _command.CommandText = ConfigurationHandler.getQuery(query);
                _command.Parameters.Clear(); 
                _connection.Open();

                if (item != null)
                {
                    Dictionary<string, string> _local = new Dictionary<string, string>();
                    _local = ConfigurationHandler.getParameter(query);
                    foreach (string _paramName in item.getParameterNameCollection())
                        _command.Parameters.Add(addParameter(_paramName,
                                    item.getParameter(_paramName),
                                    _local[_paramName], null));
                }

                object _rawData = _command.ExecuteScalar();
                _connection.Close();
                return _rawData;
            }
        #endregion

        #region Transactional Methods
        public void BeginTransaction()
        {
            _connection.Open();
            _sqlTransaction = _connection.BeginTransaction();
            _command.Connection = _connection;
            _command.Transaction = _sqlTransaction;
        }

        /// <summary>
        /// Insert a transactional query
        /// </summary>
        /// <param name="query">string query from configuration file</param>
        /// <param name="item">collection of parameters to be inserted</param>
        /// <returns>rows affected by the operation</returns>
        public int Insert(string query, SearchCriteria item)
        {
            if (ConfigurationHandler.getQueryType(query) == "sp")
                _command.CommandType = System.Data.CommandType.StoredProcedure;
            else
                _command.CommandType = System.Data.CommandType.Text;

            _command.CommandText = ConfigurationHandler.getQuery(query);
            _command.Parameters.Clear();
            if (item != null)
            {
                Dictionary<string, string> _local = new Dictionary<string, string>();
                Dictionary<string, string> _size = new Dictionary<string, string>();
                _local = ConfigurationHandler.getParameter(query);
                _size = ConfigurationHandler.getParameterSize(query);
                foreach (string _paramName in item.getParameterNameCollection())
                    _command.Parameters.Add(addParameter(_paramName,
                                item.getParameter(_paramName),
                                _local[_paramName],
                                _size[_paramName]));
            }

            int _rowAffected;
            _rowAffected = _command.ExecuteNonQuery();

            return _rowAffected;
        }

        public void ComminTransaction()
        {
            _sqlTransaction.Commit();
            _connection.Close();
        }

        public void RollbackTransaction()
        {
            _sqlTransaction.Rollback();
            _connection.Close();
        }
        #endregion

        public int getRecordsAfected
        {
            get { return _totalRecords; }
            set { _totalRecords = value; }
        }

        internal SqlParameter addParameter(string ParameterName, object ParameterValue, 
                string ParameterType, string ParameterSize)
        {
            SqlParameter _parameter = new SqlParameter();

            switch (ParameterType.ToLower())
            { 
                case "string":
                    if (ParameterSize != null)
                        if (ParameterValue.ToString().Length > int.Parse(ParameterSize))
                            ParameterValue = ((string)ParameterValue).Substring(0, int.Parse(ParameterSize));
                    break;
            }

            _parameter.ParameterName = ParameterName;
            _parameter.Value = ParameterValue;

            return _parameter;
        }

    }
}
